package com.manager.plat.dao.impl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.manager.plat.dao.ILimitCodeDao;
import com.manager.plat.util.BaseUtil;
import com.manager.plat.vo.LimitCodeVo;
/**
 * 业务总量限制数据实现层
 * @author liugeng
 *
 */
public class LimitCodeDaoImpl extends JdbcDaoSupport implements ILimitCodeDao {
	private static Logger logger = Logger.getLogger(LimitCodeDaoImpl.class);
	
	/**获得所有短号码省份管控配置信息列表*/
	@SuppressWarnings("unchecked")
	@Override
	public List<LimitCodeVo> getLimitCodeList() {
		String sql = "select lc.sp_code_id, cb.sp_code, lc.province, lc.day_limit, lc.month_limit " +
					 "from tb_ivr_limit_code lc " +
					 "inner join tb_ivr_config_business cb on cb.id = lc.sp_code_id and cb.status = 1 " +
					 "where lc.status = 1 " +
					 "group by lc.sp_code_id, cb.sp_code, lc.province, lc.day_limit, lc.month_limit";
		logger.info(String.format("获得所有短号码省份管控配置信息列表[%s]", sql));
		List<LimitCodeVo> limitCodeVoList = this.getJdbcTemplate().query(sql, rowMapper);
		
		if(limitCodeVoList != null && limitCodeVoList.size() > 0){
			return limitCodeVoList;
		}
		return null;
	}
	/**数据转换*/
	private ParameterizedRowMapper<LimitCodeVo> rowMapper = new ParameterizedRowMapper<LimitCodeVo>() {
		@Override
		public LimitCodeVo mapRow(ResultSet rs, int rowNum) throws SQLException {
			LimitCodeVo limitCodeVo = new LimitCodeVo();
			limitCodeVo.setSp_code_id(rs.getInt("sp_code_id"));
			limitCodeVo.setSp_code(rs.getString("sp_code"));
			limitCodeVo.setProvince(rs.getString("province"));
			limitCodeVo.setDay_limit(rs.getInt("day_limit"));
			limitCodeVo.setMonth_limit(rs.getInt("month_limit"));
			return limitCodeVo;
		}
	};
	/**获得短号码在当前月在各个省份已计费金额*/
	@SuppressWarnings("unchecked")
	@Override
	public Map<String, Integer> getLimitFeeMoney(String fee_month) {
		String sql = "select sp_code, province, ifnull(ceiling(sum(fee_money/100)),0) as feemoney " +
					 "from tb_ivr_limit_fee " +
					 "where fee_month = ? " +
					 "group by sp_code, province";
		Object[] params = new Object[]{fee_month};
		logger.info(String.format("获得短号码在当前月在各个省份已计费金额[%s]", BaseUtil.logSQL(sql, params)));
		List<LimitCodeVo> limitCodeVoList = this.getJdbcTemplate().query(sql, params, feeMoneyMapper);
		
		if(limitCodeVoList != null && limitCodeVoList.size() > 0){
			Map<String, Integer> month_used_map = new HashMap<String, Integer>();
			for(LimitCodeVo limitCodeVo :limitCodeVoList){
				String sp_code = limitCodeVo.getSp_code();
				String province = limitCodeVo.getProvince();
				int month_used = limitCodeVo.getMonth_used();
				month_used_map.put(sp_code + province, month_used);
			}
			return month_used_map;
		}
		return null;
	}
	/**数据转换*/
	private ParameterizedRowMapper<LimitCodeVo> feeMoneyMapper = new ParameterizedRowMapper<LimitCodeVo>() {
		@Override
		public LimitCodeVo mapRow(ResultSet rs, int rowNum) throws SQLException {
			LimitCodeVo limitCodeVo = new LimitCodeVo();
			
			limitCodeVo.setSp_code(rs.getString("sp_code"));
			limitCodeVo.setProvince(rs.getString("province"));
			limitCodeVo.setMonth_used(rs.getInt("feemoney"));
			return limitCodeVo;
		}
	};
	
	/**批量保存短号码省份管控配置信息*/
	@Override
	public int batchInsertLimitCode(List<LimitCodeVo> limitCodeList) {
		String sql = "insert into tb_ivr_limit_code(sp_code_id, province, day_limit, month_limit, status) values(?,?,?,?,1)";
		int[] count = this.getJdbcTemplate().batchUpdate(sql, new autoInsertLimitCodePsSetter(limitCodeList));
		if(count.length > 0){
			return count.length;
		}
		return 0;
	}
	
	private class autoInsertLimitCodePsSetter implements BatchPreparedStatementSetter{
		final List<LimitCodeVo> limitCodeListTemp;
		public autoInsertLimitCodePsSetter(List<LimitCodeVo> limitCodeList){
			limitCodeListTemp = limitCodeList;
		}
		
		@Override
		public void setValues(PreparedStatement ps, int i) throws SQLException {
			LimitCodeVo limitCodeVo = limitCodeListTemp.get(i);
			ps.setInt(1, limitCodeVo.getSp_code_id());
			ps.setString(2, limitCodeVo.getProvince());
			ps.setInt(3, limitCodeVo.getDay_limit());
			ps.setInt(4, limitCodeVo.getMonth_limit());
		}
		@Override
		public int getBatchSize() {
			return limitCodeListTemp.size();
		}

	}
	/**更新短号码省份管控配置信息*/
	@Override
	public int updateBusinessLimitCode(LimitCodeVo limitCodeVo) {
		String sql = "update tb_ivr_limit_code set day_limit = ?, month_limit = ? where sp_code_id = ?";
		Object[] param = new Object[]{limitCodeVo.getDay_limit(), limitCodeVo.getMonth_limit(), limitCodeVo.getSp_code_id()};
		logger.info(String.format("更新短号码省份管控配置信息[%s]", BaseUtil.logSQL(sql, param)));
		
		int count = this.getJdbcTemplate().update(sql, param);
		return count;
	}
	/**删除短号码省份管控配置信息*/
	@Override
	public int deleteBusinessLimitCode(int sp_code_id) {
		String sql = "update tb_ivr_limit_code set status = 0 where sp_code_id = ?";
		Object[] param = new Object[]{sp_code_id};
		logger.info(String.format("删除短号码省份管控配置信息[%s]", BaseUtil.logSQL(sql, param)));

		int count = this.getJdbcTemplate().update(sql, param);
		return count;
	}
	/**根据业务ID和省份信息获得短号码分省限制信息*/
	@SuppressWarnings("unchecked")
	@Override
	public LimitCodeVo getLimitCodeBySpCodeIdAndProvince(int sp_code_id, String province) {
		String sql = "select lc.id, lc.sp_code_id, cb.sp_code, lc.province, lc.day_limit, lc.month_limit " +
					 "from tb_ivr_limit_code lc " +
					 "inner join tb_ivr_config_business cb on cb.id = lc.sp_code_id and cb.status = 1 " + 
					 "where lc.sp_code_id = ? and lc.province = ? and lc.status = 1";
		Object[] param = new Object[]{sp_code_id, province};
		logger.info(String.format("根据业务ID和省份信息获得短号码分省限制信息[%s]", BaseUtil.logSQL(sql, param)));
		List<LimitCodeVo> limitCodeVoList = this.getJdbcTemplate().query(sql, param, tempMapper);
		
		if(limitCodeVoList != null && limitCodeVoList.size() > 0){
			return limitCodeVoList.get(0);
		}
		return null;
	}
	/**数据转换*/
	private ParameterizedRowMapper<LimitCodeVo> tempMapper = new ParameterizedRowMapper<LimitCodeVo>() {
		@Override
		public LimitCodeVo mapRow(ResultSet rs, int rowNum) throws SQLException {
			LimitCodeVo limitCodeVo = new LimitCodeVo();
			limitCodeVo.setId(rs.getInt("id"));
			limitCodeVo.setSp_code_id(rs.getInt("sp_code_id"));
			limitCodeVo.setSp_code(rs.getString("sp_code"));
			limitCodeVo.setProvince(rs.getString("province"));
			limitCodeVo.setDay_limit(rs.getInt("day_limit"));
			limitCodeVo.setMonth_limit(rs.getInt("month_limit"));
			return limitCodeVo;
		}
	};
	
	/**更新短号码省份管控配置信息*/
	@Override
	public int updateLimitCode(LimitCodeVo limitCodeVo) {
		String sql = "update tb_ivr_limit_code set day_limit = ?, month_limit = ? where id = ?";
		Object[] param = new Object[]{limitCodeVo.getDay_limit(), limitCodeVo.getMonth_limit(), 
									limitCodeVo.getId()};
		logger.info(String.format("更新短号码省份管控配置信息[%s]", BaseUtil.logSQL(sql, param)));
		
		int count = this.getJdbcTemplate().update(sql, param);
		return count;
	}
	/**删除短号码省份限制信息*/
	@Override
	public int deleteLimitCode(int sp_code_id, String province) {
		String sql = "update tb_ivr_limit_code set status = 0 where sp_code_id = ? and province = ?";
		Object[] param = new Object[]{sp_code_id, province};
		logger.info(String.format("删除短号码省份限制信息[%s]", BaseUtil.logSQL(sql, param)));

		int count = this.getJdbcTemplate().update(sql, param);
		return count;
	}
	/**根据业务ID获得该短号码下的所有省份*/
	@SuppressWarnings("unchecked")
	@Override
	public List<String> getBusinessLimitCodeProvinceById(int sp_code_id) {
		String sql = "select province from tb_ivr_limit_code where sp_code_id = ? and status = 1 group by province";
		Object[] param = new Object[]{sp_code_id};
		logger.info(String.format("根据业务ID获得该短号码下的所有省份[%s]", BaseUtil.logSQL(sql, param)));

		List<String> provinceList = this.getJdbcTemplate().query(sql, param, provinceMapper);
		
		if(provinceList != null && provinceList.size() > 0){
			return provinceList;
		}
		return null;
	}
	/**数据转换*/
	private ParameterizedRowMapper<String> provinceMapper = new ParameterizedRowMapper<String>() {
		@Override
		public String mapRow(ResultSet rs, int rowNum) throws SQLException {
			return rs.getString("province");
		}
	};
	
}
